#!/usr/bin/python
# -*- coding:utf-8 -*-
import csv
import re
import sqlite3

from PySide2 import QtWidgets, QtGui, QtCore
from PySide2.QtCore import QAbstractTableModel, Qt, QModelIndex
from PySide2.QtGui import QStandardItem
from PySide2.QtMultimedia import QSound
from PySide2.QtWidgets import QApplication, QFileDialog, QMessageBox, QPushButton, QWidget, QVBoxLayout, \
    QStyledItemDelegate

from fileForm_ui import Ui_fileForm
from importDataSettingWindow_ui import Ui_databaseReadWindow
from importDataSettingFunction import importDBMainWindow
from addDatabaseTable_ui import Ui_MainWindow
from addDatabaseTableFunction1 import addDatabaseTableWindow


class ImportDataSettingWindow(QtWidgets.QMainWindow, Ui_databaseReadWindow):
    def __init__(self):
        super().__init__()
        self.setupUi(self)


class addDatabaseTableSettingWindow(QtWidgets.QMainWindow, Ui_MainWindow):
    def __init__(self):
        super().__init__()
        self.setupUi(self)


class FileFormFunction(QtWidgets.QMainWindow, Ui_fileForm):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.conn = None
        self.main_wells = []  # 数据库中的主井
        self.reference_wells = []  # 数据库中的参考井
        self.import_data_disposition = []  # 导入数据的配置
        # self.import_data_disposition = [{'gasometry': ('4-4', 'D:/文件下载/项目数据/项目数据/2、主井4-4和参考井4-5/4-4-气测.txt', {'井深': ('井深', '1'), '钻时': ('钻时', '2'), '全烃': ('全烃(％)', '3')})}]
        # self.import_data_disposition= [{'resistive_sound_wave': (
        # '3-6', 'D:/文件下载/项目数据/项目数据/1、主井3-6HF和参考井3-6/3-6井三开综合测井解释数据.LAS', 'las',
        # {'DEPT': ('DEPT', '1'), 'DEV': ('DEV', '8')})}]
        self.model_tables = QtGui.QStandardItemModel()  # 添加数据表模型


        # 将按钮的点击事件连接到自定义函数
        self.pushButton.clicked.connect(self.select_database)
        self.pushButton_2.clicked.connect(self.create_database_table)
        self.radioButton.toggled.connect(self.toggle_comboBox)
        self.radioButton_5.toggled.connect(self.toggle_comboBox_reference)
        self.pushButton_10.clicked.connect(self.cancelWindow)
        self.pushButton_9.clicked.connect(self.scan_data_into_the_database)

    '''
    设置模型
    '''

    def set_model_to_tables(self, tables):
        self.model = TableModel(tables)
        self.tableView.setModel(self.model)
        # Add buttons to the third column
        for row in range(self.model.rowCount()):
            button = QPushButton("设置")
            button.clicked.connect(self.button_clicked)
            self.tableView.setIndexWidget(self.model.index(row, 2), button)

        # 设置第三列根据内容调整大小
        self.tableView.horizontalHeader().setSectionResizeMode(2, QtWidgets.QHeaderView.ResizeToContents)

        # 设置第一列和第二列平分剩余空间
        self.tableView.horizontalHeader().setSectionResizeMode(0, QtWidgets.QHeaderView.Stretch)
        self.tableView.horizontalHeader().setSectionResizeMode(1, QtWidgets.QHeaderView.Stretch)

        self.tableView_2.setModel(self.model)  # 参考井tableview
        for row in range(self.model.rowCount()):
            button = QPushButton("设置")
            button.clicked.connect(self.button_clicked_for_reference)
            self.tableView_2.setIndexWidget(self.model.index(row, 2), button)

            # 设置第三列根据内容调整大小
        self.tableView_2.horizontalHeader().setSectionResizeMode(2, QtWidgets.QHeaderView.ResizeToContents)

        # 设置第一列和第二列平分剩余空间
        self.tableView_2.horizontalHeader().setSectionResizeMode(0, QtWidgets.QHeaderView.Stretch)
        self.tableView_2.horizontalHeader().setSectionResizeMode(1, QtWidgets.QHeaderView.Stretch)

        # Set layout
        # layout = QVBoxLayout()
        # layout.addWidget(self.tableView)

        # container = QWidget()
        # container.setLayout(layout)
        # self.setCentralWidget(container)

    '''
    tableview中的设置按钮方法
    
    点击之后获取该row的表名,传入另外一个页面,根据表名获取字段 进行匹配
    '''
    def button_clicked(self):
        sender = self.sender()
        index = self.tableView.indexAt(sender.pos())
        table_name = self.model.get_data(index.row())[0]
        main_well_name = ""  # 初始化一个主井名
        # 先判断 井名是否存在
        # 判断是否连接了数据库
        if self.lineEdit.text() == "":
            QMessageBox.warning(self, "提示", "请先连接目标数据库!")
            return
        # 判断radiobutton选择的是 新建主井名 还是 更新主井信息 操作
        if self.radioButton.isChecked():
            # 选择新建主井名
            main_well_name = self.lineEdit_2.text()
            # 进行一个判断操作,判断新建的主井名是否与数据库中的主井名存在重复情况
            if main_well_name in self.main_wells:
                QMessageBox.warning(self, "提示", "该主井名数据库中已存在!")
                return
            if main_well_name == "":
                QMessageBox.warning(self, "提示", "请先输入要添加的主井名!")
                return
        elif self.radioButton_2.isChecked():
            # 选择更新主井信息 通过下拉框获取主井名
            main_well_name = self.comboBox.currentText()
            if main_well_name == "":
                QMessageBox.warning(self, "提示", "更新主井信息不能选择空")
                return
        else:
            QMessageBox.warning(self, "提示", "请先选择新建主井名或更新主井信息")
            return
        print(table_name)
        self.select_file_setting(table_name, main_well_name)  # 弹出另外一个界面

        print(f"Button clicked in row {index.row()}")

    def button_clicked_for_reference(self):
        sender = self.sender()
        index = self.tableView_2.indexAt(sender.pos())
        table_name = self.model.get_data(index.row())[0]
        reference_well_name = ""  # 初始化一个参考井名
        # 先判断 井名是否存在
        # 判断是否连接了数据库
        if self.lineEdit.text() == "":
            QMessageBox.warning(self, "提示", "请先连接目标数据库!")
            return
        # 判断radiobutton选择的是 新建参考井名 还是 更新参考井信息 操作
        if self.radioButton_5.isChecked():
            # 选择新建参考井
            reference_well_name = self.lineEdit_13.text()
            # 进行一个判断操作,判断新建的参考井名是否与数据库中的参考井名存在重复情况
            if reference_well_name in self.reference_wells:
                QMessageBox.warning(self, "提示", "该参考井名数据库中已存在!")
                return
            if reference_well_name == "":
                QMessageBox.warning(self, "提示", "请先输入要添加的参考井名!")
                return
        elif self.radioButton_6.isChecked():
            # 选择更新主井信息 通过下拉框获取主井名
            reference_well_name = self.comboBox_3.currentText()
            if reference_well_name == "":
                QMessageBox.warning(self, "提示", "更新参考井信息不能选择空")
                return
        else:
            QMessageBox.warning(self, "提示", "请先选择新建参考井或更新参考井信息")
            return
        print(table_name)
        self.select_file_setting(table_name, reference_well_name)  # 弹出另外一个界面

        print(f"Button clicked in row {index.row()}")

    def make_button_handler(self, row):
        # 创建一个处理函数，返回时绑定正确的行号
        def handle_button():
            table_name = self.model_tables.index(row, 0).data()
            print(f"按钮点击: {table_name}")
            # 在此处实现按钮点击后的操作逻辑

        return handle_button


    '''
    选择目标数据库
    '''

    def select_database(self):
        if self.sender() == self.pushButton:
            # 使用提供的样式打开文件对话框，并且只显示 .db 文件
            file_name, _ = QFileDialog.getOpenFileName(self, "选择文件", "", "数据库文件 (*.db)")
            if file_name:
                self.lineEdit.setText(file_name)
                self.create_connection(file_name)

    '''
    连接数据库
    '''

    def create_connection(self, db_file_path):
        try:
            conn = sqlite3.connect(db_file_path)
            self.conn = conn
            print("成功连接到数据库")
            self.get_database_info(conn)
            self.get_tables()
        except sqlite3.Error as e:
            print(f"连接到数据库失败: {e}")

    '''
    获取数据库信息
    '''

    def get_database_info(self, conn):
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT well_name FROM main_wells;")
            temp_main_wells = cursor.fetchall()
            self.main_wells = {well[0] for well in temp_main_wells}  # 提取每个元组的第一个元素并存储在集合中
            print(self.main_wells)
            # 清空当前comboBox中的所有项
            self.comboBox.clear()

            # 添加一个空白选项作为默认
            self.comboBox.addItem("")

            # 添加新的井名到comboBox
            for well_name in self.main_wells:
                self.comboBox.addItem(well_name)

            # 设置默认显示为空的项
            self.comboBox.setCurrentIndex(0)  # 确保第一个空白选项是默认选中的
        except sqlite3.Error as e:
            print(f"获取表信息失败: {e}")
        finally:
            if conn:
                # conn.close()
                pass

        # 获取参考井信息
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT well_name FROM reference_wells;")
            temp_reference_wells = cursor.fetchall()
            self.reference_wells = {well[0] for well in temp_reference_wells}  # 提取每个元组的第一个元素并存储在集合中
            print(self.reference_wells)
            # 清空当前comboBox中的所有项
            self.comboBox_3.clear()

            # 添加一个空白选项作为默认
            self.comboBox_3.addItem("")

            # 添加新的井名到comboBox
            for reference_well in self.reference_wells:
                self.comboBox_3.addItem(reference_well)

            # 设置默认显示为空的项
            self.comboBox_3.setCurrentIndex(0)  # 确保第一个空白选项是默认选中的
        except sqlite3.Error as e:
            print(f"获取表信息失败: {e}")
        finally:
            if conn:
                # conn.close()
                pass

    '''
    选择数据文件设置按钮
    '''

    def select_file_setting(self, table_name, main_well_name):
        # 创建 importDataSettingWindow 窗口的实例
        self.dataSettingWindow = importDBMainWindow(self.conn, table_name, main_well_name, self)
        # 设置为模态窗口
        self.dataSettingWindow.setWindowModality(QtCore.Qt.ApplicationModal)
        # 显示窗口
        self.dataSettingWindow.show()

    '''
    点击添加数据表按钮
    '''

    def create_database_table(self):
        # 判断是否连接了数据库
        if self.lineEdit.text() == "":
            QMessageBox.warning(self, "提示", "请先连接诶目标数据库!")
            return
        # 创建 addDatabaseTableWindow 窗口的实例
        self.addDatabaseTableWindow = addDatabaseTableWindow(self.conn, self)
        # 设置为模态窗口
        self.addDatabaseTableWindow.setWindowModality(QtCore.Qt.ApplicationModal)
        # 显示窗口
        self.addDatabaseTableWindow.show()

    '''
    新建主井 radio按钮被选中让 更新主井下拉框不可操作
    '''

    def toggle_comboBox(self, checked):
        self.comboBox.setEnabled(not checked)
        # 下面是设置样式
        if checked:
            pass
            # self.comboBox.setStyleSheet("QComboBox { background-color: lightgray; }")
        else:
            self.comboBox.setStyleSheet("")

    '''
       新建参考井 radio按钮被选中让 更新参考井下拉框不可操作
       '''

    def toggle_comboBox_reference(self, checked):
        self.comboBox_3.setEnabled(not checked)
        # 下面是设置样式
        if checked:
            pass
            # self.comboBox.setStyleSheet("QComboBox { background-color: lightgray; }")
        else:
            self.comboBox.setStyleSheet("")

    '''
    从数据库获取表,并将数据展示到tableview上
    '''
    def get_tables(self):
        try:
            cursor = self.conn.cursor()
            cursor.execute("SELECT table_name, description FROM table_descriptions;")
            self.table_descriptions = cursor.fetchall()
            print(self.table_descriptions)
            self.set_model_to_tables(self.table_descriptions)
        except sqlite3.Error as e:
            print(f"获取表信息失败: {e}")


    '''
    从另一个窗口返回的配置数据
    '''

    def receive_mapping_data(self, mapping_data):
        # 这里处理接收到的数据
        print("接收到的字段映射数据：", mapping_data)
        self.import_data_disposition.append(mapping_data)


    '''
    扫描数据入库
    '''

    def scan_data_into_the_database(self):
        # 判断是否连接了数据库
        if self.lineEdit.text() == "":
            QMessageBox.warning(self, "提示", "请先连接目标数据库!")
            return

        current_index = self.tabWidget.currentIndex()
        if current_index == 0:
            # print("当前选中的是'导入主井文件'")
            # 判断radiobutton选择的是 新建主井名 还是 更新主井信息 操作
            if self.radioButton.isChecked():
                # 选择新建主井名
                main_well_name = self.lineEdit_2.text()
                # 进行一个判断操作,判断新建的主井名是否与数据库中的主井名存在重复情况
                if main_well_name in self.main_wells:
                    QMessageBox.warning(self, "提示", "该主井名数据库中已存在!")
                    return
            elif self.radioButton_2.isChecked():
                # 选择更新主井信息 通过下拉框获取主井名
                main_well_name = self.comboBox.currentText()
                if main_well_name == "":
                    QMessageBox.warning(self, "提示", "该主井名不能为空!")
                    return
            else:
                QMessageBox.warning(self, "提示", "请先选择新建主井名或更新主井信息")
                return
        elif current_index == 1:
            # print("当前选中的是'导入参考井文件'")
            if self.radioButton_5.isChecked():
                reference_well_name = self.lineEdit_13.text()
                if reference_well_name in self.reference_wells:
                    QMessageBox.warning(self, "提示", "该参考井名数据库中已存在!")
                    return
            elif self.radioButton_6.isChecked():
                reference_well_name = self.comboBox_3.currentText()
                if reference_well_name == "":
                    QMessageBox.warning(self, "提示", "该参考井名不能为空!")
                    return
            else:
                QMessageBox.warning(self, "提示", "请先选择新建主井名或更新主井信息")
                return
        # print(self.import_data_disposition)

        # 判断self.import_data_disposition 变量中是否存储有配置
        if self.import_data_disposition == []:
            QMessageBox.warning(self, "提示", "请先设置要导入的文件,并且保存配置")
            return
        # 设置进度条为不确定模式
        self.progressBar.setRange(0, 0)  # 在某些Qt版本中，设置范围为0-0会启动不确定模式
        try:
            # 执行数据导入过程
            # 循环获取配置文件 进行导入数据操作
            for data in self.import_data_disposition:
                print(data)
                self.get_data_disposition(data)
        finally:
            # 数据处理完成后，将进度条设置回正常模式
            self.progressBar.setRange(0, 1)  # 将进度条设置为正常状态
            self.progressBar.setValue(1)  # 可以设置为最大值，表示完成
            # 播放完成音效
            QSound.play("complete.wav")
            # 清除配置
            self.import_data_disposition.clear()

        # QMessageBox.information(self, "完成", "所有数据已成功导入")

    '''
    获取数据配置
    '''

    def get_data_disposition(self, data):
        # 动态获取键值对
        for key, value in data.items():
            table_name = key
            well_name = value[0]
            file_path = value[1]
            import_file_type = value[2]
            disposition = value[3]

            # 打印结果
            print("Table Name:", table_name)
            print("Well Name:", well_name)
            print("File Path:", file_path)
            print("import_file_type:", import_file_type)
            print("Disposition:", disposition)
            if import_file_type == "txt":
                self.insert_data_into_table(self.conn, table_name, file_path, disposition, well_name)
            elif import_file_type == "las":
                self.insert_data_into_table_for_las(self.conn, table_name, file_path, disposition, well_name)


    def insert_data_into_table_for_las(self, db_connection, table_name, file_path, disposition, well_name):
        reading_data = False
        # encodings = ['utf-8', 'gbk', 'latin1']
        # for enc in encodings:

        with open(file_path, 'r') as file:
            # 准备 SQL 插入语句
            sql_columns = ', '.join([v for v in disposition.keys()])
            print(f"1--{sql_columns}")
            sql_placeholders = ', '.join(['?' for _ in disposition.values()])
            print(f"1--{sql_placeholders}")
            sql_query = f'INSERT INTO {table_name} (井名, {sql_columns}) VALUES (?, {sql_placeholders})'
            print("SQL Query:", sql_query)  # 打印 SQL 查询查看是否正确

            insert_success = True  # 标志变量，用于追踪是否所有插入都成功
            error_warning = ""  # 警告提示
            count = 0
            for line in file:
                count = count +1
                line = line.strip()
                if line.startswith('~ASCII'):
                    reading_data = True
                    continue
                if reading_data:
                    if line.startswith('#') or not line:
                        continue
                    data_values = re.split(r'\s+', line)
                    # print(data_values)

                    data_to_insert = [well_name]  # 开始构造要插入的数据列表，首先添加井名
                    for header, mapping in disposition.items():
                        column_name, column_index = mapping
                        # 检查索引是否有效
                        if int(column_index) - 1 < len(data_values):
                            data_to_insert.append(data_values[int(column_index) - 1])
                        else:
                            print(f"警告: 数据行{data_values}缺少列{column_index}")
                            print(f"第{count}行:{data_values}")
                            insert_success = False
                            error_warning = f"数据行缺少列{column_index}"
                            break
                    # 执行插入操作
                    try:
                        # pass
                        db_connection.execute(sql_query, data_to_insert)
                    except sqlite3.Error as e:
                        insert_success = False  # 如果任何插入失败，设置标志为False
                        print(f"数据库插入错误: {e}")
                        error_warning = e
            # 所有数据插入后检查是否所有插入都成功
            if insert_success:
                ''' 
                将井名添加到数据表中 根据不同的 tabwiget 索引 添加到主井or参考井                     
                '''
                current_index = self.tabWidget.currentIndex()
                if current_index == 0:
                    # print("当前选中的是'导入主井文件'")
                    self.add_well_name_to_database(well_name, "主井")
                elif current_index == 1:
                    # print("当前选中的是'导入参考井文件'")
                    self.add_well_name_to_database(well_name, "参考井")
                self.get_database_info(self.conn)
            else:
                QMessageBox.warning(self, "提示", f"{error_warning}")
            # 提交事务
            db_connection.commit()

    # def insert_data_into_table_for_las1(self, table_name, file_path, disposition, well_name):
    #     try:
    #         with open(file_path, 'r') as file:
    #             section = None
    #             headers = []
    #             data_lines = []
    #             for line in file:
    #                 line = line.strip()
    #                 if line.startswith('~'):
    #                     section = line.strip()
    #                 elif section == "~CURVE INFORMATION" and line and not line.startswith('#'):
    #                     parts = line.split()
    #                     if parts:
    #                         headers.append(parts[0])  # 取第一部分作为列名
    #                 elif section == "~A":
    #                     if line and not line.startswith('#'):
    #                         data_lines.append(line.split())
    #
    #         # SQL 插入语句准备
    #         sql_columns = ', '.join([v[0] for v in disposition.values()])
    #         sql_placeholders = ', '.join(['?' for _ in disposition.values()])
    #         sql_query = f'INSERT INTO {table_name} (井名, {sql_columns}) VALUES (?, {sql_placeholders})'
    #         print("SQL Query:", sql_query)
    #
    #         # 插入数据
    #         insert_success = True
    #         for data in data_lines:
    #             data_to_insert = [well_name] + [data[int(disposition[header][1]) - 1] for header in headers if
    #                                             header in disposition]
    #             try:
    #                 self.db_connection.execute(sql_query, data_to_insert)
    #             except sqlite3.Error as e:
    #                 insert_success = False
    #                 print(f"数据库插入错误: {e}")
    #
    #         # 事务提交
    #         if insert_success:
    #             self.db_connection.commit()
    #             print("数据插入成功")
    #         else:
    #             print("部分数据插入失败")
    #
    #     except Exception as e:
    #         print(f"读取LAS文件时发生错误: {e}")

    '''
    向数据库中插入数据
    '''
    def insert_data_into_table(self, db_connection, table_name, file_path, disposition, well_name):
        # 打开文件并读取数据
        encodings = ['utf-8', 'gbk', 'latin1']
        for enc in encodings:
            try:
                with open(file_path, 'r', encoding=enc) as file:
                    reader = csv.reader(file, delimiter='\t')  # 假设文件是以制表符分隔的
                    headers = next(reader)  # 读取第一行，即表头

                    print(reader)

                    # 准备 SQL 插入语句
                    sql_columns = ', '.join([v for v in disposition.keys()])
                    print(f"1--{sql_columns}")
                    sql_placeholders = ', '.join(['?' for _ in disposition.values()])
                    print(f"1--{sql_placeholders}")
                    sql_query = f'INSERT INTO {table_name} (井名, {sql_columns}) VALUES (?, {sql_placeholders})'
                    print("SQL Query:", sql_query)  # 打印 SQL 查询查看是否正确

                    insert_success = True  # 标志变量，用于追踪是否所有插入都成功
                    error_warning = ""  # 警告提示
                    # 准备要插入的数据
                    for row in reader:
                        data_to_insert = [well_name]  # 开始构造要插入的数据列表，首先添加井名
                        for header, mapping in disposition.items():
                            column_name, column_index = mapping
                            # print(f"column_name:{column_name}+ column_index:{column_index}")
                            data_to_insert.append(row[int(column_index) - 1])  # column_index是从1开始的，列表索引从0开始

                        # 执行插入操作
                        try:
                            db_connection.execute(sql_query, data_to_insert)
                        except sqlite3.Error as e:
                            insert_success = False  # 如果任何插入失败，设置标志为False
                            print(f"数据库插入错误: {e}")
                            error_warning = e
                    # 所有数据插入后检查是否所有插入都成功
                    if insert_success:
                        ''' 
                        将井名添加到数据表中 根据不同的 tabwiget 索引 添加到主井or参考井                     
                        '''
                        current_index = self.tabWidget.currentIndex()
                        if current_index == 0:
                            # print("当前选中的是'导入主井文件'")
                            self.add_well_name_to_database(well_name, "主井")
                        elif current_index == 1:
                            # print("当前选中的是'导入参考井文件'")
                            self.add_well_name_to_database(well_name, "参考井")
                        self.get_database_info(self.conn)
                    else:
                        QMessageBox.warning(self, "提示", f"{error_warning}")
                    # 提交事务
                    db_connection.commit()

            except UnicodeDecodeError:
                print(f"无法使用编码 {enc} 读取文件，尝试其他编码...")
            except Exception as e:
                print(f"读取文件时发生错误: {e}")

    '''
    添加井名到数据库
    '''

    def add_well_name_to_database(self, well_name, well_type):
        cursor = self.conn.cursor()
        if well_type == "主井":
            # 向主井表添加信息
            sql = "INSERT OR IGNORE INTO main_wells (well_name) VALUES (?)"

            cursor.execute(sql, (well_name,))
            self.conn.commit()
        elif well_type == "参考井":
            # 向参考井表添加信息
            sql = "INSERT OR IGNORE INTO reference_wells (well_name) VALUES (?)"
            cursor.execute(sql, (well_name,))
            self.conn.commit()





    '''
    取消按钮
    '''

    def cancelWindow(self):
        self.close()
        
class TableModel(QAbstractTableModel):
    def __init__(self, data):
        super(TableModel, self).__init__()
        self._data = data

    def rowCount(self, parent=QModelIndex()):
        return len(self._data)

    def columnCount(self, parent=QModelIndex()):
        return 3

    def data(self, index, role=Qt.DisplayRole):
        if role == Qt.DisplayRole:
            row = index.row()
            col = index.column()
            if col == 0:
                return self._data[row][0]
            elif col == 1:
                return self._data[row][1]
        return None

    def flags(self, index):
        if index.column() == 2:
            return Qt.ItemIsEnabled
        return Qt.ItemIsEnabled | Qt.ItemIsSelectable

    def get_data(self, row):
        return self._data[row]

    def headerData(self, section, orientation, role=Qt.DisplayRole):
        if role == Qt.DisplayRole and orientation == Qt.Horizontal:
            if section == 0:
                return "表名"
            elif section == 1:
                return "备注"
            elif section == 2:
                return "操作"
        return None




# if __name__ == "__main__":
#     app = QApplication([])
#     window = MainWindow()
#     window.show()
#     app.exec_()
